package com.ysd.dao.impl;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

import com.ysd.entity.XinWen;
import com.ysd.entity.XinWenLieBie;
import com.ysd.util.DBManger;

public class XinWenDaoImpl {
	//根据分类 Id查询对应的新闻列表
	//xwId 新闻分类Id
	public List<XinWen> getXinWenList(int xwId){
		List<XinWen> xinwenList=new ArrayList<XinWen>();
		Connection conn=DBManger.getConnection();
		Statement st=null;
		ResultSet rs=null;
		
		try {
			st=conn.createStatement();
			rs=st.executeQuery("select * from xinwenbiao_copy1 where xwWaiJianId="+xwId);
			while(rs.next()) {
				int xwid = rs.getInt("xinWenId");
				String xwname= rs.getString("xinWenName");
				int xwwaijian=rs.getInt("xwWaiJianId");
				XinWen xinwen = new XinWen(xwid, xwname, xwwaijian);
				xinwenList.add(xinwen);
			}
			return xinwenList;
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
			return null;
		}finally {
			DBManger.closeAll(conn, st, rs);
		}
		
	}
	
	public List<XinWen> getXinWenList1(){
		List<XinWen> xinwenList=new ArrayList<XinWen>();
		Connection conn=DBManger.getConnection();
		Statement st=null;
		ResultSet rs=null;
		
		try {
			st=conn.createStatement();
			rs=st.executeQuery("select * from xinwenbiao_copy1");
			while(rs.next()) {
				int xwid = rs.getInt("xinWenId");
				String xwname= rs.getString("xinWenName");
				int xwwaijian=rs.getInt("xwWaiJianId");
				XinWen xinwen = new XinWen(xwid, xwname, xwwaijian);
				xinwenList.add(xinwen);
			}
			return xinwenList;
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
			return null;
		}finally {
			DBManger.closeAll(conn, st, rs);
		}
		
	}
	
	/**
	 * 根据Id删除新闻
	 * 数据库受影响的行数
	 * @param delxw
	 * @return
	 */
	public int delXinWen(int delxw) {
		String sql = ("DELETE from xinwenbiao_copy1 where xinWenId = "+delxw);
				System.out.println(sql);
		return DBManger.executeUpdate(sql);
		
	}
	
	/**
	 * 多条件查询新闻详情
	 * 返回List 是因为在查询时可能查询到多条数据
	 */
	public List<XinWen> getXinWenByWhere(XinWen xw){
		List<XinWen> xwList = new ArrayList<XinWen>();
		
		Connection conn = DBManger.getConnection();
		Statement st = null;
		ResultSet rs = null;
		
		try {
			st = conn.createStatement();
			String sql = "select xw.*,xwfl.xinWenFebLieName from xinwenbiao_copy1 xw,xinwenfenliebiao xwfl where 1=1";
			//非空判定
			if(xw.getXinWenName()!=null&& !xw.getXinWenName().equals("")) {
				sql +=" and xinWenName LIKE '%"+xw.getXinWenName()+"%'";
			}
			
			if (xw.getXinWenNeiRong()!=null&& !xw.getXinWenNeiRong().equals("")) {
				sql +=" and xinWenNeiRong LIKE '%"+xw.getXinWenNeiRong()+"%'";
			}
			
			if(xw.getXwWaiJianId()>0) {
				sql+=" and xw.xwWaiJianId ="+xw.getXwWaiJianId();
			}
			
			sql +=" and xw.xwWaiJianId = xwfl.xwwaiJianId";
			
			System.out.println(sql);//打印拼接好的sql语句，是为了方便在控制台看sql语句是否有误
			rs=st.executeQuery(sql);
			while(rs.next()) {
				int xinWenId=rs.getInt("xinWenId");
				String xinWenName=rs.getString("xinWenName");
				int xwWaiJianId=rs.getInt("xwWaiJianId");
				String xinWenNeiRong = rs.getString("xinWenNeiRong");
				String xinWenFebLieName = rs.getString("xinWenFebLieName");
				
				XinWen xinwen = new XinWen(xinWenId, xinWenName,xwWaiJianId, xinWenNeiRong, xinWenFebLieName);
				xwList.add(xinwen);
			}
			return xwList;
			
		} catch (SQLException e) {
			e.printStackTrace();
			return null;
		}finally {
			DBManger.closeAll(conn, st, rs);
		}

	}
	
	/**
	 * 添加新闻方法
	 * 返回受影响行数
	 */
	public int addNews(XinWen xws) {
		return DBManger.executeUpdate("insert into xinwenbiao_copy1 values(null,'"+xws.getXinWenName()+"',"+xws.getXwWaiJianId()+",'"+xws.getXinWenNeiRong()+"')");
	}
	/**
	 * 根据Name查询新闻
	 * @param name
	 * @return
	 */
	public List<XinWen> getXinWenByName(String name){
		List<XinWen> xwList= new ArrayList<XinWen>();
		Connection conn = DBManger.getConnection();
		Statement st = null;
		ResultSet rs = null;
		try {
			st = conn.createStatement();
			rs = st.executeQuery("select * from xinwenbiao_copy1 where xinWenName = '"+name+"'");
			while(rs.next()) {
				int xinWenId =rs.getInt("xinWenId");
				String xinWenName = rs.getString("xinWenName");
				int xwWaiJianId = rs.getInt("xwWaiJianId");
				String xinWenNeiRong = rs.getString("xinWenNeiRong");
				XinWen xw = new XinWen(xinWenId, xinWenName, xwWaiJianId, xinWenNeiRong,"");
				xwList.add(xw);
			}
			return xwList;
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
			return null;
		}finally {
			DBManger.closeAll(conn, st, rs);
		}

	}
	
	/**
	 * 回显数据
	 */
	
	public XinWen getXWById(int Id) {
		Connection conn = DBManger.getConnection();
		Statement st = null; 
		ResultSet rs = null;
		XinWen xw = null;
		try {
			st = conn.createStatement();
			rs = st.executeQuery("select * from xinwenbiao_copy1 where xinWenId = "+Id);
			while(rs.next()) {
				int xinWenId=rs.getInt("xinWenId");
				String xinWenName=rs.getString("xinWenName");
				int xwWaiJianId = rs.getInt("xwWaiJianId");
				String xinWenNeiRong = rs.getString("xinWenNeiRong");
				
				
				 xw = new XinWen(xinWenId, xinWenName,xwWaiJianId, xinWenNeiRong, "");
			}
			return xw;
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
			return null;
		}finally {
			DBManger.closeAll(conn, st, rs);
		}
		
	}
	
	
	/**
	 * 修改新闻内容
	 */
	public int updateXW(XinWen xw) {
		String sql = "update xinwenbiao_copy1 set xinWenName ='"+xw.getXinWenName()+"',xwWaiJianId="+xw.getXwWaiJianId()+", xinWenNeiRong='"+xw.getXinWenNeiRong()+"'  where xinWenId = "+xw.getXinWenId();
		
		System.out.println(sql);
		return DBManger.executeUpdate(sql);
	}
	
	
	
	
	
	
	
	
	
	
	
	
	
}
